﻿using API_Service.DBHelper;
using Dapper;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace API_Service.BizLogic.SOPControl
{
    public class ESOPService: BaseLogicService
    {
        public ExecutionResult<dynamic> GetSOPInfo(string vGroupName, string vSKUName,string vLineName =null)
        {
            var result = new ExecutionResult<dynamic>();
            try
            {
                List<dynamic> _lstSOP = new List<dynamic>();
                dynamic _sopInfo = new Dictionary<string, object>();
                if (!string.IsNullOrEmpty(vGroupName) && !string.IsNullOrEmpty(vSKUName))
                {
                    result = this.QuerySOPConfigInfo(vGroupName, vSKUName);
                    if (result.Status && result.Datas.Length>0)
                    {
                        _sopInfo = result.Datas[0];
                        var _sopDetails = new Dictionary<string, string>();
                        var _sopImages = new List<string>();
                        var sopDetails = result.Datas[0].sop_details;
                        if (sopDetails != null && sopDetails.Length > 0)
                        {
                            foreach (var tmp in sopDetails)
                            {
                                _sopDetails.Add(tmp.sequence.ToString(), tmp.sequence.ToString() + ":" + (string)tmp.sop_detail_description);
                                var sopImages = tmp.sop_images;
                                if (sopImages != null && sopImages.Length > 0)
                                {
                                    foreach (var tmpImg in sopImages)
                                    {
                                        //_sopImages.Add((string)tmpImg.image_name, (string)tmpImg.image_path);
                                        _sopImages.Add((string)tmpImg.image_path);
                                    }
                                }
                            }
                        }
                        _sopInfo.sop_details = _sopDetails;
                        _sopInfo.sop_images = _sopImages;
                        _lstSOP.Add(_sopInfo);
                        result.Status = true;
                        result.Datas = _lstSOP.ToArray();
                        result.Message = "查询SOP信息成功..";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = string.Format("系统无[站别: {0}, 机种: {1}]的SOP资料，请确认是否维护。", vGroupName, vSKUName);
                    }
                }
                else
                {
                    result.Status = true;
                    result.Message = "站别和机种未传，系统恢复默认SOP说明";
                    result.Datas = null;
                }
                Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "--" + result.Message);
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = "查询SOP信息异常。";
                result.Datas = null; 
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
            }
            return result;
        }
        public ExecutionResult<dynamic> QuerySOPConfigInfo(string vGroupName =null, string vSKUName =null)
        {
            var result = new ExecutionResult<dynamic>();
            
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    //dynamic tmpSOPInfo = null;
                    List<dynamic> _lstSOP = new List<dynamic>();
                    //get SOP base info
                    result = this.QuerySOPBase(vGroupName, vSKUName);
                    if (result.Status)
                    {                        
                        for(var j = 0; j < result.Datas.Length; j++)
                        {
                           var tmpSOPInfo = result.Datas[j];
                            //GET SOP Detail info
                           var _tmpResult = this.QuerySOPDetail(tmpSOPInfo.id);
                            if (_tmpResult.Datas.Length > 0)
                            {
                                tmpSOPInfo.sop_details = _tmpResult.Datas;
                                for (var i = 0; i < tmpSOPInfo.sop_details.Length; i++)
                                {
                                    //GET SOP  images info
                                    var _imgResult = this.QuerySOPImages(tmpSOPInfo.sop_details[i].id);
                                    if (_imgResult.Status)
                                        tmpSOPInfo.sop_details[i].sop_images = _imgResult.Datas;
                                }
                            }
                            _lstSOP.Add(tmpSOPInfo);
                        }

                        result.Status = true;
                        result.Datas = _lstSOP.ToArray();
                        result.Message = "查询SOP信息成功。";
                        Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + result.Message);
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "SOP配置信息错误，同一站[" + vGroupName + "],同一SKU[" + vSKUName + "] 存在笔SOP信息。";
                    }
                    
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询SOP配置信息异常。";   
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> AddSOPBase(dynamic vSOPBase)
        {
            //判断是否存在Detail资料，若存在，则同步添加SOP Detail & SOP Images;
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            dynamic tmpSOPInfo = JsonConvert.DeserializeObject<dynamic>(vSOPBase);

            //塞入SOP基础表信息
            result = this.InsertSOPBaseInfo(tmpSOPInfo);
            if (result.Status)
            {
                int _esopId = result.ExecuteCount;
                var _sopDetails = tmpSOPInfo.sop_details;
                if(_sopDetails != null)
                {
                    foreach(var tmpDetail in _sopDetails)
                    {
                        //取的当前insert到DB的SOP ID
                        tmpDetail.sop_name_id = _esopId;
                        //塞入SOP操作步骤信息
                        result = this.InsertSOPDetailInfo(tmpDetail);
                        if (result.Status)
                        {
                            var _detail_id = result.ExecuteCount;
                            var _sopImages = tmpDetail.sop_images;
                            if(_sopImages != null)
                            {
                                foreach(var tmpImages in _sopImages)
                                {
                                    //当前insert到DB的detail ID
                                    tmpImages.sop_detail_id = _detail_id;
                                    //塞入图片信息
                                    result = this.InsertSOPImagesInfo(tmpImages);
                                }
                            }
                        }
                    }
                }
            }
            return result;
        }
        public ExecutionResult<dynamic> UpdateSOPBase(dynamic vSOPBase)
        {
            //判断是否存在Detail资料，若存在，则同步添加SOP Detail & SOP Images;
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            dynamic tmpSOPInfo = JsonConvert.DeserializeObject<dynamic>(vSOPBase);

            result = this.UpdateSOPBaseInfo(tmpSOPInfo);
            if (result.Status)
            {
                var _sopDetails = tmpSOPInfo.sop_details;
                if ( _sopDetails.Count>0)
                {
                    foreach (var tmpDetail in _sopDetails)
                    {
                        //更新sop detail数据
                        if (!String.IsNullOrEmpty((string)tmpDetail.id))
                        {
                            result = this.UpdateSOPDetailInfo(tmpDetail);                           
                        }
                        else
                        {
                            result = this.InsertSOPDetailInfo(tmpDetail);
                            if (result.Status)
                                tmpDetail.id = result.ExecuteCount;
                        }
                        if (result.Status)
                        {
                            var _sopImages = tmpDetail.sop_images;
                            if (_sopImages != null)
                            {
                                foreach (var tmpImage in _sopImages)
                                {
                                    //更新SOP图片信息
                                    if (!String.IsNullOrEmpty((string)tmpImage.id))
                                    {
                                        result = this.UpdateSOPImageInfo(tmpImage);
                                    }
                                    else
                                    {
                                        tmpImage.sop_detail_id = tmpDetail.id;
                                        result = this.InsertSOPImagesInfo(tmpImage);
                                    }
                                }
                            }
                            else
                            {
                                //删除SOP图片资料，by detial ID
                                result = this.DeleteSOPImageInfo(0, tmpDetail.id);
                            }
                        }
                    }
                }
                else
                {
                    //删除所有Detail数据及图片数据 by SOP id
                    result = this.DeleteSOPDetailAndImagesBySOPId((int)tmpSOPInfo.id);
                }
            }
            return result;
        }
        public ExecutionResult<dynamic> DeleteSOPBase(dynamic vId)
        {
            return this.DeleteSOPBaseInfo(vId);
        }
        private ExecutionResult<dynamic> QuerySOPBase(string vGroup =null,string vSKUName = null)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"SELECT * FROM [dbo].[c_sop_base_info_t] as a 
                                              WHERE  a.is_valid = 'Y' ";
                    var dbParams = new DynamicParameters();
                    if (!string.IsNullOrEmpty(vGroup))
                    {
                        sql = sql + " AND a.group_name LIKE @group_name ";
                        dbParams.Add("@group_name", "%" + vGroup + "%", DbType.String, ParameterDirection.Input);
                    }
                    if (!string.IsNullOrEmpty(vSKUName))
                    {
                        sql = sql + " AND a.sku_name LIKE @sku_name";
                        dbParams.Add("@sku_name", "%" + vSKUName + "%", DbType.String, ParameterDirection.Input);
                    }

                    result.Datas = conn.Query<dynamic>(sql, dbParams).ToArray();
                    result.Status = true;
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "--" + sql);
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询SOP基础信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> InsertSOPBaseInfo(dynamic vSOPBase)
        {
            var result = new ExecutionResult<dynamic>();
            #region SQL and parameters
            string sql = @"INSERT INTO [dbo].[c_sop_base_info_t]
                                               ([sop_name],[group_name],[sku_name],[operation_step],[description]
                                               ,[remarks],[is_valid],[create_time],[create_user])
                                         VALUES (@sop_name,@group_name,@sku_name,@operation_step,@description,
	                                     @remarks,@is_valid,GETDATE(),@create_user)
                                        SELECT @sop_id = SCOPE_IDENTITY()";
            var dbParams = new DynamicParameters();
            dbParams.Add("@sop_name", (string)vSOPBase.sop_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@group_name", (string)vSOPBase.group_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sku_name", (string)vSOPBase.sku_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@operation_step", (string)vSOPBase.operation_step, DbType.String, ParameterDirection.Input);
            dbParams.Add("@description", (string)vSOPBase.description, DbType.String, ParameterDirection.Input);
            dbParams.Add("@remarks", (string)vSOPBase.remarks, DbType.String, ParameterDirection.Input);
            dbParams.Add("@is_valid", "Y", DbType.String, ParameterDirection.Input);
            dbParams.Add("@create_user", (string)vSOPBase.create_user, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sop_id", 5, DbType.Int32, ParameterDirection.Output);
            #endregion

            using (IDbConnection conn  = SqlCreator.Create())
            {
                try
                {
                    var res = conn.Execute(sql, dbParams);
                    result.ExecuteCount = dbParams.Get<int>("sop_id");
                    if (res > 0)
                    {
                        result.Status = true;
                        result.Message = "新增SOP基础信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "新增SOP基础信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "新增SOP基础资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateSOPBaseInfo(dynamic vSOPBase)
        {
            var result = new ExecutionResult<dynamic>();
            #region SQL and parameters
            string sql = @"UPDATE [dbo].[c_sop_base_info_t]
                                               SET [sop_name] = @sop_name,[group_name] = @group_name,[sku_name] = @sku_name,
                                                     [operation_step] = @operation_step, [description] = @description,[remarks] = @remarks,[is_valid] = @is_valid,
		                                             [update_time] = @update_time,[update_user] = @update_user
                                             WHERE [id]=@id";
            var dbParams = new DynamicParameters();
            dbParams.Add("@sop_name", (string)vSOPBase.sop_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@group_name", (string)vSOPBase.group_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sku_name", (string)vSOPBase.sku_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@operation_step", (string)vSOPBase.operation_step, DbType.String, ParameterDirection.Input);
            dbParams.Add("@description", (string)vSOPBase.description, DbType.String, ParameterDirection.Input);
            dbParams.Add("@remarks", (string)vSOPBase.remarks, DbType.String, ParameterDirection.Input);
            dbParams.Add("@is_valid", (string)vSOPBase.is_valid, DbType.String, ParameterDirection.Input);
            dbParams.Add("@update_user", (string)vSOPBase.update_user, DbType.String, ParameterDirection.Input);
            dbParams.Add("@update_time", (DateTime)vSOPBase.update_time, DbType.DateTime, ParameterDirection.Input);
            dbParams.Add("@id", (Int32)vSOPBase.id, DbType.Int32, ParameterDirection.Input);
            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(sql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新SOP基础信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新SOP基础信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新SOP基础资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> DeleteSOPBaseInfo(dynamic vId)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string _insertSql = @"DELETE FROM [dbo].[c_sop_base_info_t] WHERE[id] = @id";
                    var dbParams = new DynamicParameters();

                    dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "删除SOP基础信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "删除SOP基础信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "删除SOP基础资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QuerySOPDetail(int vSOPId)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                   string sql = @"SELECT * FROM [dbo].[c_sop_detail_info_t] as a 
                                              WHERE  a.is_valid = 'Y' AND a.sop_name_id=@sopId ";
                    result.Datas = conn.Query<dynamic>(sql, new { sopId = vSOPId }).ToArray();
                    result.Status = true;
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询SOP详细信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> InsertSOPDetailInfo(dynamic vSOPDetail)
        {
            var result = new ExecutionResult<dynamic>();
            #region SQL and parameters
            string sql = @"INSERT INTO [dbo].[c_sop_detail_info_t]
                                               ([sop_name_id],[step_name],[sop_detail_description],[sequence],[is_valid],[create_time],[create_user])
                                         VALUES(@sop_name_id,@step_name,@sop_detail_description,@sequence,@is_valid,GETDATE(),@create_user);
                                          SELECT @detail_id = SCOPE_IDENTITY()";
            var dbParams = new DynamicParameters();
            dbParams.Add("@sop_name_id", (Int32)vSOPDetail.sop_name_id, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@step_name", (string)vSOPDetail.step_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sop_detail_description", (string)vSOPDetail.sop_detail_description, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sequence", (Int32)vSOPDetail.sequence, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@is_valid", "Y", DbType.String, ParameterDirection.Input);
            dbParams.Add("@create_user", (string)vSOPDetail.create_user, DbType.String, ParameterDirection.Input);
            dbParams.Add("@detail_id", 5, DbType.Int32, ParameterDirection.Output);
            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    var res = conn.Execute(sql, dbParams);
                    result.ExecuteCount = dbParams.Get<int>("@detail_id");
                    if (res > 0)
                    {
                        result.Status = true;
                        result.Message = "新增SOP详细信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "新增SOP详细信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "新增SOP详细资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateSOPDetailInfo(dynamic vSOPDetail)
        {
            var result = new ExecutionResult<dynamic>();
            #region SQL and parameters
            string sql = @"UPDATE [dbo].[c_sop_detail_info_t]
                                               SET [sop_name_id] = @sop_name_id,[step_name] = @step_name,[sop_detail_description] = @sop_detail_description,
	                                               [sequence] = @sequence,[is_valid] = @is_valid,[update_time] = @update_time,[update_user] = @update_user
                                             WHERE [id]=@id";
            var dbParams = new DynamicParameters();
            dbParams.Add("@sop_name_id", (Int32)vSOPDetail.sop_name_id, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@step_name", (string)vSOPDetail.step_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sop_detail_description", (string)vSOPDetail.sop_detail_description, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sequence", (Int32)vSOPDetail.sequence, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@is_valid", (string)vSOPDetail.is_valid, DbType.String, ParameterDirection.Input);
            dbParams.Add("@update_user", (string)vSOPDetail.update_user, DbType.String, ParameterDirection.Input);
            dbParams.Add("@update_time", (DateTime)vSOPDetail.update_time, DbType.DateTime, ParameterDirection.Input);
            dbParams.Add("@id", (Int32)vSOPDetail.id, DbType.Int32, ParameterDirection.Input);
            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(sql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新SOP详细信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新SOP详细信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新SOP详细资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> DeleteSOPDetailInfobyId(int vId)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string _insertSql = @"DELETE FROM [dbo].[c_sop_detail_info_t] WHERE[id] = @id";
                    var dbParams = new DynamicParameters();

                    dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "删除SOP详细信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "删除SOP详细信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "删除SOP详细资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QuerySOPImages(int vDetailId)
        {
            var result = new ExecutionResult<dynamic>();
            using(IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"SELECT * FROM [dbo].[c_sop_images_info_t] as a 
                                              WHERE  a.is_valid = 'Y' AND a.sop_detail_id=@sopId ";
                    result.Datas = conn.Query<dynamic>(sql, new { sopId = vDetailId }).ToArray();
                    result.Status = true;                    
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询SOP图片信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> InsertSOPImagesInfo(dynamic vSOPImage)
        {
            var result = new ExecutionResult<dynamic>();
            #region
            string sql = @"INSERT INTO [dbo].[c_sop_images_info_t]([sop_detail_id],[image_name],[image_path],
			                                            [sequence],[is_valid],[create_time],[create_user])
                                            VALUES(@sop_detail_id,@image_name,@image_path,@sequence,@is_valid,GETDATE(),@create_user)
                                         SELECT @image_id = SCOPE_IDENTITY()";
            var dbParams = new DynamicParameters();
            dbParams.Add("@sop_detail_id", (Int32)vSOPImage.sop_detail_id, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@image_name", (string)vSOPImage.image_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@image_path", (string)vSOPImage.image_path, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sequence", (Int32)vSOPImage.sequence, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@is_valid", "Y", DbType.String, ParameterDirection.Input);
            dbParams.Add("@create_user", (string)vSOPImage.create_user, DbType.String, ParameterDirection.Input);
            dbParams.Add("@create_time", (DateTime)vSOPImage.create_time, DbType.DateTime, ParameterDirection.Input);
            dbParams.Add("@image_id", 5, DbType.Int32, ParameterDirection.Output);
            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {                   
                    var res = conn.Execute(sql, dbParams);
                    result.ExecuteCount = dbParams.Get<int>("image_id");
                    if (res > 0)
                    {
                        result.Status = true;
                        result.Message = "新增SOP图片信息成功.";

                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "新增SOP图片信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "新增SOP图片资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateSOPImageInfo(dynamic vSOPImage)
        {
            var result = new ExecutionResult<dynamic>();
            #region SQL and parameters
            string sql = @"UPDATE [dbo].[c_sop_images_info_t]
                                               SET [sop_detail_id] = @sop_detail_id,[image_name] = @image_name,[image_path] = @image_path,
	                                               [sequence] = @sequence,[is_valid] = @is_valid,[update_time] = @update_time,[update_user] = @update_user
                                             WHERE [id]=@id";
            var dbParams = new DynamicParameters();
            dbParams.Add("@sop_detail_id", (Int32)vSOPImage.sop_detail_id, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@image_name", (string)vSOPImage.image_name, DbType.String, ParameterDirection.Input);
            dbParams.Add("@image_path", (string)vSOPImage.image_path, DbType.String, ParameterDirection.Input);
            dbParams.Add("@sequence", (Int32)vSOPImage.sequence, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@is_valid", (string)vSOPImage.is_valid, DbType.String, ParameterDirection.Input);
            dbParams.Add("@update_user", (string)vSOPImage.update_user, DbType.String, ParameterDirection.Input);
            dbParams.Add("@update_time", (DateTime)vSOPImage.update_time, DbType.DateTime, ParameterDirection.Input);
            dbParams.Add("@id", (Int32)vSOPImage.id, DbType.Int32, ParameterDirection.Input);
            #endregion 
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(sql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新SOP图片信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新SOP图片信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新SOP图片资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> DeleteSOPImageInfo(int vId =0,int vDetailId=0)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string _delSql = @"DELETE FROM [dbo].[c_sop_images_info_t] WHERE 1=1 ";
                    var dbParams = new DynamicParameters();

                   if(vDetailId > 0)
                    {
                        _delSql = _delSql + " AND [sop_detail_id] = @sop_detail_id";
                        dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);
                    }
                    if (vId > 0)
                    {
                        _delSql = _delSql + " AND [id] = @id";
                        dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);
                    }
                    else
                    {
                        if (vDetailId <= 0)
                        {
                            _delSql = _delSql + " AND [id] = @id";
                            dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);
                        }
                    }

                    result.ExecuteCount = conn.Execute(_delSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "删除SOP图片信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "删除SOP图片信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "删除SOP图片资料异常。";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }

        private ExecutionResult<dynamic> DeleteSOPDetailAndImagesBySOPId(int vSopId)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            string sql_detail = "DELETE FROM [dbo].[c_sop_detail_info_t] WHERE [sop_name_id]=@sop_name_id ";
            string sql_images = @"DELETE FROM [dbo].[c_sop_images_info_t] WHERE [sop_detail_id] IN (
                                SELECT [id] FROM [dbo].[c_sop_detail_info_t] WHERE [sop_name_id]=@sop_name_id) ";
            using(IDbConnection conn = SqlCreator.Create())
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

               IDbTransaction _dbTrans =  conn.BeginTransaction();
                try
                {
                    //delete images
                    conn.Execute(sql_images, new { sop_name_id = vSopId }, _dbTrans, null, null);
                    //delete details
                    conn.Execute(sql_detail, new { sop_name_id = vSopId }, _dbTrans, null, null);

                    //commit 
                    _dbTrans.Commit();
                    result.Status = true;
                    result.Message = "DELETE SOP detail info and images by SOP ID success.";
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ". Msg:" + result.Message);
                }
                catch(Exception ex)
                {
                    _dbTrans.Rollback();
                    result.Status = false;
                    result.Message = "DELTE SOP detail and image info by SOP id Exception. Msg：" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }

        private ExecutionResult<dynamic> DeleteSOPDetailAndImagesByDetailId(int vDetailId)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            string sql_detail = "DELETE FROM [dbo].[c_sop_detail_info_t] WHERE [sop_name_id]=@sop_detail_id ";
            string sql_images = @"DELETE FROM [dbo].[c_sop_images_info_t] WHERE [sop_detail_id] =@sop_detail_id ";
            using (IDbConnection conn = SqlCreator.Create())
            {
                IDbTransaction _dbTrans = conn.BeginTransaction();
                try
                {
                    //delete images
                    conn.Execute(sql_images, new { sop_detail_id = vDetailId }, _dbTrans, null, null);
                    //delete details
                    conn.Execute(sql_detail, new { sop_detail_id = vDetailId }, _dbTrans, null, null);

                    //commit 
                    _dbTrans.Commit();
                    result.Status = true;
                    result.Message = "DELETE SOP detail info and images by SOP Detail ID success.";
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ". Msg:" + result.Message);
                }
                catch (Exception ex)
                {
                    _dbTrans.Rollback();
                    result.Status = false;
                    result.Message = "DELTE SOP detail and image info by SOP detail id Exception. Msg：" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;

        }
    }
}